package cn.easy.kitchen.data;

import cn.easy.base.utils.BaseUtils;
import cn.easy.base.utils.DateTimeUtil;
import cn.easy.kitchen.domain.*;
import cn.easy.kitchen.service.*;
import cn.easy.kitchen.utils.ProjectUtil;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.env.Environment;
import org.springframework.stereotype.Service;

import javax.annotation.PostConstruct;
import javax.annotation.PreDestroy;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.*;

@Service
public class ImportFromDB {
    private Logger logger = LoggerFactory.getLogger(this.getClass());
    private JdbcUtils jdbcUtils = null;
    private Date startAt = DateTimeUtil.getStartMonth(2017, 0);
    private Date endAt = DateTimeUtil.getEndMonth(2017, 6);
    @Autowired
    private StatMaterialPriceService statMaterialPriceService;
    @Autowired
    private StatMaterialStructureService statMaterialStructureService;
    @Autowired
    private CustomerService customerService;
    @Autowired
    private StatNutritionService statNutritionService;
    @Autowired
    private NutritionService nutritionService;
    @Autowired
    private MaterialOrderService materialOrderService;
    @Autowired
    private MaterialBaseService materialBaseService;
    @Autowired
    private Environment env;

    @PostConstruct
    public void init() {
        if(env == null ? false : env.acceptsProfiles("dev")) {
            try {
                jdbcUtils = new JdbcUtils(new OldDatabaseMeta());
                logger.info("初始化数据库连接成功！");
            } catch (ClassNotFoundException | SQLException e) {
                logger.error("初始化数据库连接失败：" + e.getMessage());
                e.printStackTrace();
            }
        }
    }

    @PreDestroy
    public void destroy() {
        if (jdbcUtils != null) {
            jdbcUtils.releaseConn();
            jdbcUtils = null;
            logger.info("释放数据库连接成功");
        }
    }

    public void importOrder() {
        importMaterialStructure();
        importMaterialPrice();
        importMaterialNutrition();
        importMaterialOrder();
    }

    /**
     * 食材结构
     */
    private void importMaterialStructure() {
        String sql = "select be.MBE_Code as code,convert(varchar(10), od.MOB_DemandTime, 20) as date_on, SUM(mod_count) as count from v_order_detail od, MT_Basic_Enterprise be where od.MOB_PurchaserId = be.MBE_ID and od.mct_name='诸暨教育学校MY' " +
                "and od.MOB_DemandTime > ? and od.MOB_DemandTime < ? and material_type_name like ? group by convert(varchar(10), od.MOB_DemandTime, 20), be.MBE_Code";

        List<StatMaterialStructure> statMaterialStructureList = new ArrayList<>();
        Map<String, StatMaterialStructure> dateOnAndCode2StatMaterialStructureMap = new HashMap<>();

        List<Object> params = new ArrayList<>();
        params.add(startAt);
        params.add(endAt);
        params.add("%肉%");
        List<Map<String, Object>> result = jdbcUtils.findMoreResult(sql, params);
        for (Map<String, Object> map : result) {
            String code = BaseUtils.getMapStrVal(map, "code");
            Date dateOn = ProjectUtil.getDateVal(map, "date_on");

            String key = getKey(code, dateOn);

            StatMaterialStructure statMaterialStructure = null;
            if (dateOnAndCode2StatMaterialStructureMap.containsKey(key)) {
                statMaterialStructure = dateOnAndCode2StatMaterialStructureMap.get(key);
            } else {
                statMaterialStructure = new StatMaterialStructure();
                statMaterialStructure.setDataOn(dateOn);

                setCustomerId(statMaterialStructure, code);

                dateOnAndCode2StatMaterialStructureMap.put(key, statMaterialStructure);
                statMaterialStructureList.add(statMaterialStructure);
            }

            statMaterialStructure.setMeat(BaseUtils.getMapBigDecimalVal(map, "count"));
        }

        params = new ArrayList<>();
        params.add(startAt);
        params.add(endAt);
        params.add("%蔬菜%");
        result = jdbcUtils.findMoreResult(sql, params);
        for (Map<String, Object> map : result) {
            String code = BaseUtils.getMapStrVal(map, "code");
            Date dateOn = ProjectUtil.getDateVal(map, "date_on");

            String key = getKey(code, dateOn);

            StatMaterialStructure statMaterialStructure = null;
            if (dateOnAndCode2StatMaterialStructureMap.containsKey(key)) {
                statMaterialStructure = dateOnAndCode2StatMaterialStructureMap.get(key);
            } else {
                statMaterialStructure = new StatMaterialStructure();
                statMaterialStructure.setDataOn(dateOn);

                setCustomerId(statMaterialStructure, code);

                dateOnAndCode2StatMaterialStructureMap.put(key, statMaterialStructure);
                statMaterialStructureList.add(statMaterialStructure);
            }

            statMaterialStructure.setVegetables(BaseUtils.getMapBigDecimalVal(map, "count"));
        }

        params = new ArrayList<>();
        params.add(startAt);
        params.add(endAt);
        params.add("%水产%");
        result = jdbcUtils.findMoreResult(sql, params);
        for (Map<String, Object> map : result) {
            String code = BaseUtils.getMapStrVal(map, "code");
            Date dateOn = ProjectUtil.getDateVal(map, "date_on");

            String key = getKey(code, dateOn);

            StatMaterialStructure statMaterialStructure = null;
            if (dateOnAndCode2StatMaterialStructureMap.containsKey(key)) {
                statMaterialStructure = dateOnAndCode2StatMaterialStructureMap.get(key);
            } else {
                statMaterialStructure = new StatMaterialStructure();
                statMaterialStructure.setDataOn(dateOn);

                setCustomerId(statMaterialStructure, code);

                dateOnAndCode2StatMaterialStructureMap.put(key, statMaterialStructure);
                statMaterialStructureList.add(statMaterialStructure);
            }

            statMaterialStructure.setAquaticProducts(BaseUtils.getMapBigDecimalVal(map, "count"));
        }

        statMaterialStructureService.save(statMaterialStructureList);
    }

    private String getKey(String code, Date dateOn) {
        return code + "_" + DateTimeUtil.toDateTimeString(dateOn);
    }

    private void setCustomerId(StatMaterialStructure statMaterialStructure, String code) {
        if (StringUtils.isNotBlank(statMaterialStructure.getCustomerId())) {
            return;
        }
        Customer customer = customerService.findByOldNo(code);
        if (customer != null) {
            statMaterialStructure.setCustomerId(customer.getId());
        } else {
            logger.error("找不到code为" + code + "的客户信息");
        }
    }

    /**
     * 食材价格
     */
    private void importMaterialPrice() {
        String sql = " select od.MCM_Name as name, convert(varchar(10), od.MOB_DemandTime, 20) as date_on, AVG(od.mod_price) as price from v_order_detail od where od.mct_name='诸暨教育学校MY'" +
                " and od.MOB_DemandTime > ? and od.MOB_DemandTime < ? and od.MCM_Name in('猪肉','玉米','生菜', '大米') group by convert(varchar(10), od.MOB_DemandTime, 20), od.MCM_Name";
        List<Object> params = new ArrayList<>();
        params.add(startAt);
        params.add(endAt);
        List<Map<String, Object>> result = jdbcUtils.findMoreResult(sql, params);
        List<StatMaterialPrice> statMaterialPriceList = new ArrayList<>();
        Map<Date, StatMaterialPrice> dateOn2StatMaterialPriceMap = new HashMap<>();
        for (Map<String, Object> map : result) {
            Date dateOn = ProjectUtil.getDateVal(map, "date_on");

            Date key = dateOn;

            StatMaterialPrice statMaterialPrice = null;
            if (dateOn2StatMaterialPriceMap.containsKey(key)) {
                statMaterialPrice = dateOn2StatMaterialPriceMap.get(key);
            } else {
                statMaterialPrice = new StatMaterialPrice();
                statMaterialPrice.setDataOn(dateOn);
                dateOn2StatMaterialPriceMap.put(key, statMaterialPrice);
                statMaterialPriceList.add(statMaterialPrice);
            }
            String name = BaseUtils.getMapStrVal(map, "name");
            BigDecimal price = BaseUtils.getMapBigDecimalVal(map, "price");
            if ("猪肉".equals(name)) {
                statMaterialPrice.setPork(price);
            } else if ("玉米".equals(name)) {
                statMaterialPrice.setCorn(price);
            } else if ("生菜".equals(name)) {
                statMaterialPrice.setRomaineLettuce(price);
            } else if ("大米".equals(name)) {
                statMaterialPrice.setRice(price);
            }
        }
        statMaterialPriceService.save(statMaterialPriceList);
    }

    /**
     * 营养摄入
     */
    private void importMaterialNutrition() {
        String sql = "select mbe.mbe_code,od.mob_demandTime, sum(od.mod_count) as mod_count,od.mod_object from v_order_detail od " +
                "left join MT_Basic_Enterprise mbe on od.mob_purchaserId = mbe.MBE_ID where " +
                "od.mob_demandTime >=? " +
                "and od.mob_demandTime <=? " +
                "and od.mct_name='诸暨教育学校MY' " +
                "and material_type_name like '%菜%' " +  //这里只是为了过滤数据，实在量太大
                "and od.mob_demandTime is not null " +
                "group by mbe.mbe_code, od.mod_object,od.mob_demandTime " +
                "order by mbe.mbe_code asc ";
        List<Object> params = new ArrayList<>();
        params.add(startAt);
        params.add(endAt);
        List<Map<String, Object>> result = jdbcUtils.findMoreResult(sql, params);
        List<StatNutrition> statNutritionList = new ArrayList<>();
        if (result != null && !result.isEmpty()) {
            statNutritionService.deleteAll();
            Map<String, String> traceElementMap = ProjectUtil.getTraceElementMap();
            Customer customer = null;
            for (Map<String, Object> map : result) {
                StatNutrition statNutrition = new StatNutrition();
                String oldNo = BaseUtils.getMapStrVal(map, "mbe_code");
                if (customer != null && customer.getOldNo().equals(oldNo)) {
                    statNutrition.setCustomerId(customer.getId());
                } else {
                    customer = customerService.findByOldNo(oldNo);
                    statNutrition.setCustomerId(customer == null ? null : customer.getId());
                }
                Date dateOn = ProjectUtil.getDateVal(map, "mob_demandTime");
                statNutrition.setDataOn(dateOn);
                BigDecimal modCount = ProjectUtil.getBigDecimalVal(map, "mod_count");
                String mod_object = BaseUtils.getMapStrVal(map, "mod_object");
                statNutrition = fillNutrition(statNutrition, mod_object, modCount, traceElementMap);
                statNutritionList.add(statNutrition);
            }
        }
        statNutritionService.save(statNutritionList);
    }

    /**
     * @param statNutrition
     * @param materialNo    //物料编码
     * @param weight        //重量
     * @return
     */
    private StatNutrition fillNutrition(StatNutrition statNutrition, String materialNo, BigDecimal weight, Map<String, String> traceElementMap) {
        List<Object[]> nutritionList = nutritionService.findNutritionByOldMaterialNo(weight, materialNo);
        if (nutritionList != null && !nutritionList.isEmpty()) {
            for (Object[] obj : nutritionList) {
                if(obj[1] == null){
                    continue;
                }
                String nutritionName = obj[1].toString();
                BigDecimal contentValue = ProjectUtil.getBigDecimalVal(obj[0]);
                switch (nutritionName) {
                    case "蛋白质":
                        statNutrition.setProtein(contentValue);
                        break;
                    case "脂肪":
                        statNutrition.setFat(contentValue);
                        break;
                    case "糖":
                        statNutrition.setSugar(contentValue);
                        break;
                    case "膳食纤维":
                        statNutrition.setDietaryFiber(contentValue);
                        break;
                }
                //若属于微量元素,则累加
                if (traceElementMap.get(nutritionName) != null) {
                    statNutrition.setTraceElement(statNutrition.getTraceElement().add(contentValue));
                }
            }
        }
        return statNutrition;
    }

    /**
     * 订单保存
     */
    private void importMaterialOrder() {
        String sql = "select mbe.mbe_code,od.mod_object,sum(od.mod_count) as mod_count, od.mob_demandTime from v_order_detail od " +
                "left join MT_Basic_Enterprise mbe on od.mob_purchaserId = mbe.MBE_ID where " +
                "od.mob_demandTime >=? " +
                "and od.mob_demandTime <=? " +
                "and od.mct_name='诸暨教育学校MY' " +
                "and od.mob_demandTime is not null " +
                "group by mbe.mbe_code, od.mod_object, od.mob_demandTime " +
                "order by mbe.mbe_code asc ";
        List<Object> params = new ArrayList<>();
        params.add(startAt);
        params.add(endAt);
        List<Map<String, Object>> result = jdbcUtils.findMoreResult(sql, params);
        List<MaterialOrder> materialOrderList = new ArrayList<>();
        if (result != null && !result.isEmpty()) {
            materialOrderService.deleteAll();
            Customer customer = null;
            for (Map<String, Object> map : result) {
                MaterialOrder materialOrder = new MaterialOrder();
                String oldNo = BaseUtils.getMapStrVal(map, "mbe_code");
                if (customer != null && customer.getOldNo().equals(oldNo)) {
                    materialOrder.setCustomerId(customer.getId());
                } else {
                    customer = customerService.findByOldNo(oldNo);
                    materialOrder.setCustomerId(customer == null ? null : customer.getId());
                }
                Date dateOn = ProjectUtil.getDateVal(map, "mob_demandTime");
                materialOrder.setDataOn(dateOn);
                BigDecimal modCount = ProjectUtil.getBigDecimalVal(map, "mod_count");
                materialOrder.setMaterialWeight(modCount);

                String modObject = BaseUtils.getMapStrVal(map, "mod_object");
                MaterialBase materialBase = materialBaseService.findByOldNo(modObject);
                if (materialBase != null) {
                    materialOrder.setMaterialBaseId(materialBase.getId());
                }
                materialOrderList.add(materialOrder);
            }
        }
        materialOrderService.save(materialOrderList);
    }
}
